package cn.spiral.user.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.spiral.core.service.impl.BaseServiceImpl;
import cn.spiral.role.entity.Role;
import cn.spiral.user.dao.UserDao;
import cn.spiral.user.entity.User;
import cn.spiral.user.entity.UserRole;
import cn.spiral.user.entity.UserRoleId;
import cn.spiral.user.service.UserService;

@Service("userService")
public class UserServiceImpl extends BaseServiceImpl<User> implements UserService{

	private UserDao userDao;
	
	@Autowired
	public void setUserDao(UserDao userDao) {
		super.setBaseDao(userDao);
		this.userDao = userDao;
	}

	@Override
	public void delete(Serializable id) {
		userDao.deleteUserRoleByUserId((String) id);
		userDao.delete(id);
	}

	@Override
	public void exportExcel(List<User> userList, ServletOutputStream outputStream) {
		//创建工作簿
		HSSFWorkbook workbook=new HSSFWorkbook();
		//合并单元格对象需要单独创建在添加到工作表中
		CellRangeAddress cellRangeAddress= new CellRangeAddress(0,0,0,4);//参数一次为起始行，结束行，起始列，结束列
		//设置头标题样式
		HSSFCellStyle styleHead=this.createCellStyle(workbook, (short) 16);
		//设置列标题样式
		HSSFCellStyle styleColumn=this.createCellStyle(workbook, (short) 13);
		
		//创建工作表
		HSSFSheet sheet=workbook.createSheet("用户列表");
		sheet.addMergedRegion(cellRangeAddress);
		//设置默认列宽
		sheet.setDefaultColumnWidth(25);
		
		//创建头标题行
		HSSFRow rowHead=sheet.createRow(0);
		HSSFCell cellHead=rowHead.createCell(0);
		cellHead.setCellStyle(styleHead);
		cellHead.setCellValue("用户列表");
		
		//创建标题行
		HSSFRow rowColumn=sheet.createRow(1);
		String[] titles={"用户名","帐号", "所属部门", "性别", "电子邮箱"};
		for(int i=0;i<titles.length;i++){
			HSSFCell cellColumn=rowColumn.createCell(i);
			cellColumn.setCellStyle(styleColumn);
			cellColumn.setCellValue(titles[i]);
		}
		
		//将userList内容写入单元格
		if(userList!=null){
			for(int i=0;i<userList.size();i++){
				HSSFRow row=sheet.createRow(i+2);
				HSSFCell cell11=row.createCell(0);
				cell11.setCellValue(userList.get(i).getName());
				HSSFCell cell12=row.createCell(1);
				cell12.setCellValue(userList.get(i).getAccount());
				HSSFCell cell13=row.createCell(2);
				cell13.setCellValue(userList.get(i).isGender()?"男":"女");
				HSSFCell cell14=row.createCell(3);
				cell14.setCellValue(userList.get(i).getEmail());
			}
		}
		
		try {
			workbook.write(outputStream);
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	//设置单元格格式通用方法抽取
	private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook,short fontSize){
		HSSFCellStyle style=workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		//创建字体
		HSSFFont font=workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体
		font.setFontHeightInPoints(fontSize);
		style.setFont(font);//加载字体
		return style;
	}

	@Override
	public void importExcel(File userExcel, String userExcelFileName) {
		
		try {
			InputStream inputStream=new FileInputStream(userExcel);
			//判断是否为03以下版本excel
			boolean is03Excel=userExcelFileName.matches("^.+\\.(?i)(xls)$");
			Workbook workbook= is03Excel ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
			//读取第一个工作表
			Sheet sheet=workbook.getSheetAt(0);
			
			//读取行,如果少于三行认为无有效数据
			if(sheet.getPhysicalNumberOfRows()>2){
				
				User user=null;
				for(int k = 2; k < sheet.getPhysicalNumberOfRows(); k++){
					//读取单元格
					Row row = sheet.getRow(k);
					user = new User();
					//用户名
					Cell cell0 = row.getCell(0);
					user.setName(cell0.getStringCellValue());
					//帐号
					Cell cell1 = row.getCell(1);
					user.setAccount(cell1.getStringCellValue());
					//所属部门
					Cell cell2 = row.getCell(2);
					user.setDept(cell2.getStringCellValue());
					//性别
					Cell cell3 = row.getCell(3);
					user.setGender(cell3.getStringCellValue().equals("男"));
					//手机号
					String mobile = "";
					Cell cell4 = row.getCell(4);
					try {
						mobile = cell4.getStringCellValue();
					} catch (Exception e) {
						//excel的数字会用科学计数法显示，这里采用大数据读取然后再转为字符串
						double dMobile = cell4.getNumericCellValue();
						mobile = BigDecimal.valueOf(dMobile).toString();
					}
					user.setMobile(mobile);
					
					//电子邮箱
					Cell cell5 = row.getCell(5);
					user.setEmail(cell5.getStringCellValue());
					//生日
					Cell cell6 = row.getCell(6);
					if(cell6.getDateCellValue() != null){
						user.setBirthday(cell6.getDateCellValue());
					}
					//默认用户密码为 123456
					user.setPassword("123456");
					//默认用户状态为 有效
					user.setState(User.USER_STATE_VALID);
					
					//5、保存用户
					this.save(user);
				}
			}
			workbook.close();
			inputStream.close();
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}

	//校验账号唯一性
	@Override
	public List<User> findUserByAccountAndId(String id, String account) {
		return userDao.findUserByAccountAndId(id, account);
	}

	//没有级联，要更新User和UserRole表
	//保存用户和角色
	@Override
	public void saveUserAndRole(User user, String... roleIds) {
		//先保存用户
		userDao.save(user);
		//如果角色不为空，保存用户对应角色
		if(roleIds!=null){
			for (String roleId : roleIds) {
				Role role = new Role(roleId);
				userDao.saveUserRole(new UserRole(new UserRoleId(role,user.getId())));
			}
		}
	}
	
	//更新用户和角色
	@Override
	public void updateUserAndRole(User user, String... roleIds) {
		//先更新用户
		userDao.update(user);
		//UserRole表是联合主键，不能进行单纯的更新操作，先根据用户id删除该用户所有信息，然后再重新增加
		userDao.deleteUserRoleByUserId(user.getId());
		if(roleIds!=null){
			for (String roleId : roleIds) {
				Role role = new Role(roleId);
				UserRoleId userRoleId = new UserRoleId(role,user.getId());
				UserRole userRole = new UserRole(userRoleId);
				userDao.saveUserRole(userRole);
			}
		}
	}

	//通过用户id查找用户所拥有的角色
	@Override
	public List<UserRole> getUserRolesByUserId(String id) {
		return userDao.getUserRolesByUserId(id);
	}

	@Override
	public List<User> findUserByAccountAndPass(String account, String password) {
		return userDao.findUserByAccountAndPass(account, password);
	}
}
